public async Task <List <HourlyHitsDto> > GetHourlyChartAsync(string shortCode, int hours = 24)
{
var startDate = DateTimeOffset.UtcNow
.AddHours(-hours)
.AddMinutes(-DateTimeOffset.UtcNow.Minute)
.AddSeconds(-DateTimeOffset.UtcNow.Second);
var table = await _tableFactory.GetCloudTableReferenceAsync(TableNames.HitsPerHour);
var partitionKeyFilter = TableQuery.GenerateFilterCondition(
nameof(HitsAggregateHourlyEntity.PartitionKey),
QueryComparisons.Equal,
PartitionKeys.HitsPerHour);
var shortCodeFilter = TableQuery.GenerateFilterCondition(
nameof(HitsAggregateHourlyEntity.ShortCode),
QueryComparisons.Equal,
shortCode);
var dateFilter = TableQuery.GenerateFilterConditionForDate(
nameof(HitsAggregateHourlyEntity.AggregateRangeStart),
QueryComparisons.GreaterThanOrEqual,
startDate);
var queryFilter = TableQuery.CombineFilters(partitionKeyFilter, TableOperators.And,
TableQuery.CombineFilters(dateFilter, TableOperators.And, shortCodeFilter));
var query = new TableQuery <HitsAggregateHourlyEntity>().Where(queryFilter);
var segment = await table.ExecuteQuerySegmentedAsync(query, null);
var list = segment.Results.Select(ent => new HourlyHitsDto
{
Start = ent.AggregateRangeStart,
Hour = ent.AggregateRangeStart.ToString("HH:mm"),
Hits = ent.TotalHits
}).ToList();
startDate = startDate.AddHours(1);
do
{
var hourString = $"{startDate:HH}:00";
if (!list.Any(x => x.Hour.Equals(hourString)))
{
list.Add(new HourlyHitsDto
{
Start = startDate,
Hour = hourString,
Hits = 0
});
}
startDate = startDate.AddHours(1);
} while (startDate < DateTimeOffset.UtcNow);
return(list.OrderBy(x => x.Start).ToList());
}
public async Task <List <DailyHitsDto> > GetDailyChartAsync(string shortCode, int days = 30)
{
var startDate = DateTimeOffset.UtcNow
.AddDays(-days)
.AddHours(-DateTimeOffset.UtcNow.Hour)
.AddMinutes(-DateTimeOffset.UtcNow.Minute)
.AddSeconds(-DateTimeOffset.UtcNow.Second);
var table = await _tableFactory.GetCloudTableReferenceAsync(TableNames.HitsPerDay);
var partitionKeyFilter = TableQuery.GenerateFilterCondition(
nameof(HitsAggregateHourlyEntity.PartitionKey),
QueryComparisons.Equal,
PartitionKeys.HitsPerDay);
var shortCodeFilter = TableQuery.GenerateFilterCondition(
nameof(HitsAggregateHourlyEntity.ShortCode),
QueryComparisons.Equal,
shortCode);
var dateFilter = TableQuery.GenerateFilterConditionForDate(
nameof(HitsAggregateHourlyEntity.AggregateRangeStart),
QueryComparisons.GreaterThanOrEqual,
startDate);
var queryFilter = TableQuery.CombineFilters(partitionKeyFilter, TableOperators.And,
TableQuery.CombineFilters(dateFilter, TableOperators.And, shortCodeFilter));
var query = new TableQuery <HitsAggregateHourlyEntity>().Where(queryFilter);
var segment = await table.ExecuteQuerySegmentedAsync(query, null);
var list = segment.Results.Select(ent => new DailyHitsDto
{
Start = ent.AggregateRangeStart,
Hits = ent.TotalHits
}).ToList();
startDate = startDate.AddHours(1);
do
{
if (!list.Any(ent => ent.Start.Day.Equals(startDate.Day) &&
ent.Start.Month.Equals(startDate.Month) &&
ent.Start.Year.Equals(startDate.Year)))
{
list.Add(new DailyHitsDto
{
Hits = 0,
Start = new DateTimeOffset(startDate.Year, startDate.Month, startDate.Day, 0, 0, 0, TimeSpan.FromSeconds(0))
});
}
startDate = startDate.AddDays(1);
} while (startDate < DateTimeOffset.UtcNow);
return(list.OrderBy(x => x.Start).ToList());
}
public static async Task <HttpResponseMessage> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = null)] HttpRequestMessage req,
[StorageAccount("StorageConnectionString")] CloudStorageAccount storageAccount,
TraceWriter log
)
{
var client = storageAccount.CreateCloudTableClient();
string partitionKey = Authenticator.Authenticate(client, req);
if (string.IsNullOrEmpty(partitionKey))
{
return(req.CreateResponse(HttpStatusCode.Unauthorized));
}
long timestamp;
var timestampString = req.GetQueryNameValuePairs()
.FirstOrDefault(q => string.Compare(q.Key, "timestamp", true) == 0)
.Value;
if (!long.TryParse(timestampString, out timestamp))
{
return(req.CreateResponse(
HttpStatusCode.BadRequest,
"The query parameter \"timestamp\" is mandatory for item queries"
));
}
else if (timestamp < MinimumEpoch)
{
return(req.CreateResponse(
HttpStatusCode.BadRequest,
"The query parameter \"timestamp\" is not a valid epoch timestamp"
));
}
DateTimeOffset laterThan = DateTimeOffset.FromUnixTimeMilliseconds(timestamp);
log.Info($"User {partitionKey} has requested an item download for items newer than {laterThan}");
var itemTable = client.GetTableReference(Item.TableName);
var query = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, partitionKey),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.GreaterThan, laterThan)
);
var exQuery = new TableQuery <Item>().Where(query);
var filteredItems = itemTable.ExecuteQuery(exQuery).Where(m => m.IsActive);
log.Info($"A total of {filteredItems.Count()} items were returned");
return(req.CreateResponse(HttpStatusCode.OK, filteredItems));
}
public static async Task <IList <AppointmentEntity> > GetAppointmentsAsync(string repo, DateTime startDate, DateTime endDate)
{
TableQuery <AppointmentEntity> query = new TableQuery <AppointmentEntity>()
.Where(TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, repo),
TableOperators.And,
TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForDate("DateTime", QueryComparisons.GreaterThanOrEqual, startDate),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("DateTime", QueryComparisons.LessThanOrEqual, endDate))));
return(await GetAppointmentsAsync(query));
}
/// <inheritdoc/>
public async Task <IList <EmailNotificationItemEntity> > GetPendingOrFailedEmailNotificationsByDateRange(DateTimeRange dateRange, string applicationName, List <NotificationItemStatus> statusList, bool loadBody = false)
{
if (dateRange == null || dateRange.StartDate == null || dateRange.EndDate == null)
{
throw new ArgumentNullException(nameof(dateRange));
}
string filterExpression = TableQuery.GenerateFilterConditionForDate("SendOnUtcDate", QueryComparisons.GreaterThanOrEqual, dateRange.StartDate)
+ " and "
+ TableQuery.GenerateFilterConditionForDate("SendOnUtcDate", QueryComparisons.LessThan, dateRange.EndDate);
if (!string.IsNullOrEmpty(applicationName))
{
filterExpression = filterExpression
+ " and "
+ TableQuery.GenerateFilterCondition("Application", QueryComparisons.Equal, applicationName);
}
if (statusList != null && statusList.Count > 0)
{
string statusFilterExpression = null;
foreach (var status in statusList)
{
string filter = TableQuery.GenerateFilterCondition("Status", QueryComparisons.Equal, status.ToString());
statusFilterExpression = statusFilterExpression == null ? filter : " or " + filter;
}
filterExpression = TableQuery.CombineFilters(filterExpression, TableOperators.And, statusFilterExpression);
}
this.logger.TraceInformation($"Started {nameof(this.GetPendingOrFailedEmailNotificationsByDateRange)} method of {nameof(TableStorageEmailRepository)}.");
List <EmailNotificationItemTableEntity> emailNotificationItemEntities = new List <EmailNotificationItemTableEntity>();
var linqQuery = new TableQuery <EmailNotificationItemTableEntity>().Where(filterExpression);
emailNotificationItemEntities = this.emailHistoryTable.ExecuteQuery(linqQuery)?.Select(ent => ent).ToList();
if (emailNotificationItemEntities == null || emailNotificationItemEntities.Count == 0)
{
return(null);
}
IList <EmailNotificationItemEntity> notificationEntities = emailNotificationItemEntities.Select(e => this.ConvertToEmailNotificationItemEntity(e)).ToList();
IList <EmailNotificationItemEntity> updatedNotificationEntities = notificationEntities;
if (!string.IsNullOrEmpty(applicationName) && loadBody)
{
updatedNotificationEntities = await this.mailAttachmentRepository.DownloadEmail(notificationEntities, applicationName).ConfigureAwait(false);
}
this.logger.TraceInformation($"Finished {nameof(this.GetPendingOrFailedEmailNotificationsByDateRange)} method of {nameof(TableStorageEmailRepository)}.");
return(updatedNotificationEntities);
}
DictionaryTableEntity GetDictionaryTableEntity(Type type, string property, object value)
{
var tableName = type.Name;
var table = client.GetTableReference(tableName);
TableQuery <DictionaryTableEntity> query;
var propertyInfo = type.GetProperty(property);
if (propertyInfo.PropertyType == typeof(byte[]))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBinary(property, QueryComparisons.Equal, (byte[])value));
}
else if (propertyInfo.PropertyType == typeof(bool))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBool(property, QueryComparisons.Equal, (bool)value));
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDate(property, QueryComparisons.Equal, (DateTime)value));
}
else if (propertyInfo.PropertyType == typeof(Guid))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForGuid(property, QueryComparisons.Equal, (Guid)value));
}
else if (propertyInfo.PropertyType == typeof(Int32))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForInt(property, QueryComparisons.Equal, (int)value));
}
else if (propertyInfo.PropertyType == typeof(Int64))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForLong(property, QueryComparisons.Equal, (long)value));
}
else if (propertyInfo.PropertyType == typeof(Double))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDouble(property, QueryComparisons.Equal, (double)value));
}
else if (propertyInfo.PropertyType == typeof(string))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterCondition(property, QueryComparisons.Equal, (string)value));
}
else
{
throw new NotSupportedException(
string.Format("The property type '{0}' is not supported in windows azure table storage",
propertyInfo.PropertyType.Name));
}
var tableEntity = table.ExecuteQuery(query).SafeFirstOrDefault();
return(tableEntity);
}
private static async Task <SlackErrorsResponse> ValidatePlan(IBinder binder, DialogSubmissionPayload dsp)
{
var response = new SlackErrorsResponse();
var now = DateTime.Now;
if (!Utils.TryParseDate(dsp.GetValue("date"), out var date))
{
response.AddError("date", "Le format de date n'est pas valide.");
}
else if (date < now.Date)
{
response.AddError("date", "La date ne peut pas être dans le passé.");
}
else if (now.TimeOfDay > TimeSpan.Parse("12:00") && date == now.Date)
{
response.AddError("date", "Comme il est passé midi, la date ne peut pas être aujourd'hui.");
}
if (!response.Valid)
{
return(response);
}
var plansTable = await binder.GetTable("plans");
var query = new TableQuery <Plan>().Where(TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", "eq", dsp.PartitionKey),
"and",
TableQuery.GenerateFilterConditionForDate("Date", "eq", date.AddHours(12))
));
var plans = await plansTable.ExecuteQueryAsync(query);
if (plans.Any())
{
response.AddError("date", "Un Lunch & Watch est déjà prévu pour cette date.");
}
if (date == now.Date)
{
if (string.IsNullOrWhiteSpace(dsp.GetValue("owner")))
{
response.AddError("owner", "Comme la présentation est prévue aujourd'hui, un responsable doit être désigné.");
}
if (string.IsNullOrWhiteSpace(dsp.GetValue("video")))
{
response.AddError("video", "Comme la présentation est prévue aujourd'hui, un vidéo doit être choisi.");
}
}
return(response);
}
string GetConditions()
{
var conditions = new List <string>();
if (default(DateTime) != this.CreatedTimeFrom)
{
conditions.Add(TableQuery.GenerateFilterConditionForDate("CreatedTime", QueryComparisons.GreaterThanOrEqual, new DateTimeOffset(this.CreatedTimeFrom)));
}
if (default(DateTime) != this.CreatedTimeTo)
{
conditions.Add(TableQuery.GenerateFilterConditionForDate("CreatedTime", QueryComparisons.LessThanOrEqual, new DateTimeOffset(this.CreatedTimeTo)));
}
if (this.RuntimeStatus != null && this.RuntimeStatus.Any())
{
string runtimeCondition = this.RuntimeStatus.Select(x => TableQuery.GenerateFilterCondition("RuntimeStatus", QueryComparisons.Equal, x.ToString()))
.Aggregate((a, b) => TableQuery.CombineFilters(a, TableOperators.Or, b));
if (runtimeCondition.Count() != 0)
{
conditions.Add(runtimeCondition);
}
}
if (this.TaskHubNames != null)
{
string taskHubCondition = this.TaskHubNames.Select(x => TableQuery.GenerateFilterCondition("TaskHubName", QueryComparisons.Equal, x.ToString()))
.Aggregate((a, b) => TableQuery.CombineFilters(a, TableOperators.Or, b));
if (taskHubCondition.Count() != 0)
{
conditions.Add(taskHubCondition);
}
}
if (!string.IsNullOrEmpty(this.InstanceIdPrefix))
{
int length = this.InstanceIdPrefix.Length - 1;
char incrementedLastChar = (char)(this.InstanceIdPrefix[length] + 1);
string greaterThanPrefix = this.InstanceIdPrefix.Substring(0, length) + incrementedLastChar;
conditions.Add(TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, InstanceIdPrefix),
TableOperators.And,
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, greaterThanPrefix)));
}
return(conditions.Count == 1 ?
conditions[0] :
conditions.Aggregate((a, b) => TableQuery.CombineFilters(a, TableOperators.And, b)));
}
/// <summary>
/// Enumerates the items in a table looking for items with a timestamp over the time to keep.
/// </summary>
/// <param name="table">CloudTable instance.</param>
/// <returns>Number of items removed.</returns>
internal async Task <int> EnumerateTableItemsAsync(CloudTable table)
{
if (null == table)
{
throw new ArgumentNullException("Argument is null", nameof(this.EnumerateTableItemsAsync));
}
Stopwatch sw = Stopwatch.StartNew();
ServiceEventSource.Current.Trace("EnumerateTableItemsAsync", table.Name);
TableBatchOperation tbo = new TableBatchOperation();
string pKey = null;
int deleteCount = 0;
TableContinuationToken tct = null;
TableQuery query =
new TableQuery().Where(
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.LessThan, DateTimeOffset.Now.Subtract(this._timeToKeep)));
do
{
// Execute the query.
TableQuerySegment <DynamicTableEntity> queryResult = await table.ExecuteQuerySegmentedAsync(query, tct).ConfigureAwait(false);
tct = queryResult.ContinuationToken;
foreach (DynamicTableEntity item in queryResult.Results)
{
// Remember the current partition key, creating a batch with the same key for efficient deletions.
if ((pKey != item.PartitionKey) || (MaximumBatchSize == tbo.Count))
{
// Remove the items, pause and then start the next batch.
deleteCount += await this.RemoveItemsAsync(table, tbo).ConfigureAwait(false);
await Task.Delay(100);
tbo.Clear();
}
pKey = item.PartitionKey;
tbo.Delete(item);
}
// Delete any last items.
deleteCount += await this.RemoveItemsAsync(table, tbo).ConfigureAwait(false);
} while ((null != tct) && (deleteCount < this._targetCount));
ServiceEventSource.Current.Trace($"EnumerateTableItemsAsync removed {deleteCount} items from {table.Name} in {sw.ElapsedMilliseconds}ms.");
return(deleteCount);
}
public ActionResult OnService(int?months, string userInfo)
{
if (User.Identity.Name != "*****@*****.**")
{
return(RedirectToAction("Index", "Home"));
}
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(CloudConfigurationManager.GetSetting("MS_AzureStorageAccountConnectionString"));
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("UserInformation");
if (months.HasValue && userInfo != null)
{
var value = userInfo.Split('^');
var OwnerID = value[0];
var ShopLocation = value[1];
TableOperation retrieveOperation = TableOperation.Retrieve <UserInfoEntity>(OwnerID, ShopLocation);
TableResult retrievedResult = table.Execute(retrieveOperation);
UserInfoEntity updateEntity = (UserInfoEntity)retrievedResult.Result;
// Print the phone number of the result.
if (updateEntity != null)
{
updateEntity.Period = updateEntity.Period.AddMonths(months.Value);
TableOperation updateOperation = TableOperation.Replace(updateEntity);
table.Execute(updateOperation);
}
}
List <UserInfoEntity> result = new List <UserInfoEntity>();
List <UserInfoEntity> resultInPeriod = new List <UserInfoEntity>();
TableQuery <UserInfoEntity> queryInPeriod = new TableQuery <UserInfoEntity>().Where(
TableQuery.GenerateFilterConditionForDate("Period", QueryComparisons.GreaterThanOrEqual, DateTime.Now));
foreach (UserInfoEntity entity in table.ExecuteQuery(queryInPeriod))
{
if (entity.Paid)
{
entity.Period = entity.Period.AddHours(9);
resultInPeriod.Add(entity);
}
}
result = resultInPeriod;
return(View(result));
}
public static TableQuery <DictionaryTableEntity> BuildWherePropertyQuery(Type type, string property, object value)
{
TableQuery <DictionaryTableEntity> query;
var propertyInfo = type.GetProperty(property);
if (propertyInfo == null)
{
return(null);
}
if (propertyInfo.PropertyType == typeof(byte[]))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBinary(property, QueryComparisons.Equal, (byte[])value));
}
else if (propertyInfo.PropertyType == typeof(bool))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBool(property, QueryComparisons.Equal, (bool)value));
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDate(property, QueryComparisons.Equal, (DateTime)value));
}
else if (propertyInfo.PropertyType == typeof(Guid))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForGuid(property, QueryComparisons.Equal, (Guid)value));
}
else if (propertyInfo.PropertyType == typeof(int))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForInt(property, QueryComparisons.Equal, (int)value));
}
else if (propertyInfo.PropertyType == typeof(long))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForLong(property, QueryComparisons.Equal, (long)value));
}
else if (propertyInfo.PropertyType == typeof(double))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDouble(property, QueryComparisons.Equal, (double)value));
}
else if (propertyInfo.PropertyType == typeof(string))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterCondition(property, QueryComparisons.Equal, (string)value));
}
else
{
throw new NotSupportedException($"The property type '{propertyInfo.PropertyType.Name}' is not supported in Azure Table Storage");
}
return(query);
}
public Task <Either <DomainError, Unit> > RemoveExpired()
{
var filterByPartition = TableQuery.GenerateFilterCondition("PartitionKey",
QueryComparisons.Equal, "feed-processed");
var filterByDate = TableQuery.GenerateFilterConditionForDate("Timestamp",
QueryComparisons.LessThanOrEqual, DateTimeOffset.Now);
var queryFilter = TableQuery.CombineFilters(filterByPartition, TableOperators.And, filterByDate);
var tableQuery = new TableQuery <ProcessedTitlesStorage>().Where(queryFilter);
var result = TableUtils.TryGetAllTableElements(_tableClient, tableQuery);
return(result.BindAsync(BatchDelete));
}
public IEnumerable <InnerScanStatusEntity> LoadMeasurementData(string tag, DateTimeOffset begin, DateTimeOffset end)
{
var tagCond =
TableQuery.GenerateFilterCondition("Tag", QueryComparisons.Equal, tag);
var measurementDateCond =
TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForDate("MeasurementDate", QueryComparisons.GreaterThanOrEqual, begin),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("MeasurementDate", QueryComparisons.LessThan, end));
var cond = TableQuery.CombineFilters(tagCond, TableOperators.And, measurementDateCond);
var query = new TableQuery <InnerScanStatusEntity>().Where(cond);
return(table.ExecuteQuery(query, entityResolver));
}
private string GenerateFilterCondition(string column, Filter.ECompare filterOperator, ETypeCode compareDataType, object value)
{
string filterString;
switch (compareDataType)
{
case ETypeCode.String:
case ETypeCode.Text:
case ETypeCode.Json:
case ETypeCode.Xml:
case ETypeCode.Guid:
case ETypeCode.Unknown:
filterString = TableQuery.GenerateFilterCondition(column, ConvertOperator(filterOperator), (string)value);
break;
case ETypeCode.Boolean:
filterString = TableQuery.GenerateFilterConditionForBool(column, ConvertOperator(filterOperator), (bool)value);
break;
case ETypeCode.Int16:
case ETypeCode.Int32:
case ETypeCode.UInt16:
case ETypeCode.UInt32:
filterString = TableQuery.GenerateFilterConditionForInt(column, ConvertOperator(filterOperator), (int)value);
break;
case ETypeCode.UInt64:
case ETypeCode.Int64:
filterString = TableQuery.GenerateFilterConditionForLong(column, ConvertOperator(filterOperator), (long)value);
break;
case ETypeCode.DateTime:
filterString = TableQuery.GenerateFilterConditionForDate(column, ConvertOperator(filterOperator), (DateTime)value);
break;
case ETypeCode.Time:
filterString = TableQuery.GenerateFilterCondition(column, ConvertOperator(filterOperator), value.ToString());
break;
case ETypeCode.Double:
case ETypeCode.Decimal:
filterString = TableQuery.GenerateFilterConditionForDouble(column, ConvertOperator(filterOperator), (double)value);
break;
default:
throw new Exception("The data type: " + compareDataType.ToString() + " is not supported by Azure table storage.");
}
return(filterString);
}
private string GetConditions()
{
var conditions = new List <string>();
if (default(DateTime) != this.CreatedTimeFrom)
{
conditions.Add(TableQuery.GenerateFilterConditionForDate("CreatedTime", QueryComparisons.GreaterThanOrEqual, new DateTimeOffset(this.CreatedTimeFrom)));
}
if (default(DateTime) != this.CreatedTimeTo)
{
conditions.Add(TableQuery.GenerateFilterConditionForDate("CreatedTime", QueryComparisons.LessThanOrEqual, new DateTimeOffset(this.CreatedTimeTo)));
}
if (default(DateTime) != this.LastUpdatedTimeFrom)
{
conditions.Add(TableQuery.GenerateFilterConditionForDate("LastUpdatedTime", QueryComparisons.GreaterThanOrEqual, new DateTimeOffset(this.LastUpdatedTimeFrom)));
}
if (default(DateTime) != this.LastUpdatedTimeTo)
{
conditions.Add(TableQuery.GenerateFilterConditionForDate("LastUpdatedTime", QueryComparisons.LessThanOrEqual, new DateTimeOffset(this.LastUpdatedTimeTo)));
}
if (!string.IsNullOrEmpty(this.RuntimeStatus))
{
conditions.Add(TableQuery.GenerateFilterCondition("RuntimeStatus", QueryComparisons.Equal, this.RuntimeStatus));
}
if (conditions.Count == 1)
{
return(conditions[0]);
}
else
{
string lastCondition = null;
foreach (var condition in conditions)
{
if (string.IsNullOrEmpty(lastCondition))
{
lastCondition = condition;
continue;
}
lastCondition = TableQuery.CombineFilters(lastCondition, TableOperators.And, condition);
}
return(lastCondition);
}
}
public async Task RunAsync(
[TimerTrigger("* */30 * * * *")] TimerInfo myTimer,
[Table("AdUrls", Connection = "AzureWebJobsStorage")] CloudTable adsTable,
ILogger log)
{
log.LogInformation($"Crawl started at: {DateTime.Now}");
// websrape Ads List page.
var adsSummarizedPage = await _scraper.GetHtmlContentAsync(_realEstateWebSiteAdsListSettings.AdsListPageUrl);
// get all links for ads from adsListPage
var crawledAds = _crawler.GetAdsSummaries(adsSummarizedPage);
// get saved ads from storage.
var condition = TableQuery.GenerateFilterConditionForDate(
"Timestamp",
QueryComparisons.GreaterThan,
DateTimeOffset.UtcNow.AddMinutes(-15)
);
var selectQuery = new TableQuery <CrawledAdSummary>() /*.Where(condition)*/;
var savedAds = await adsTable.ExecuteQuerySegmentedAsync(selectQuery, null);
// find unseen ones.
var newAds = crawledAds.Where(w => !savedAds.Any(a => a.Url == w.Url)).ToList();
if (newAds.Count != 0)
{
// notify for new ads.
await _notifier.SendNotificationForNewAdsAsync(newAds);
// save new ads in storage.
var insertBatchOperation = new TableBatchOperation();
newAds.ForEach(a =>
{
a.AssignParitionAndRowKey();
insertBatchOperation.Add(TableOperation.InsertOrReplace(a));
});
await adsTable.ExecuteBatchAsync(insertBatchOperation);
}
log.LogInformation($"Crawl ended at: {DateTime.Now}");
}
public List <object> GetAllSessionSOSAndTrackHistory(long startTicks, long endTicks, bool sosFlag = true)
{
try
{
int rowCount = 1;
TableQuery <SessionHistory> UQuery = null;
string filterInSOS = TableQuery.GenerateFilterConditionForBool("InSOS", QueryComparisons.Equal, sosFlag);
string filterTimeStamp = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForDate("SessionStartTime", QueryComparisons.GreaterThanOrEqual, new DateTime(startTicks)),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("SessionStartTime", QueryComparisons.LessThanOrEqual, new DateTime(endTicks).AddHours(24))
);
UQuery = new TableQuery <SessionHistory>().Where(TableQuery.CombineFilters(filterInSOS, TableOperators.And, filterTimeStamp));
base.LoadTableSilent(Constants.SessionHistoryTableName);
var qryReturn = base.EntityTable.ExecuteQuery(UQuery).ToList()
.Select(sessionHist => new
{
MobileNumber = sessionHist.MobileNumber,
UserName = sessionHist.Name,
InSOS = sessionHist.InSOS,
NoOfSMSRecipients = sessionHist.NoOfSMSRecipients,
NoOfEmailRecipients = sessionHist.NoOfEmailRecipients,
NoOfSMSSents = sessionHist.NoOfSMSSent,
NoOfEmailsSents = sessionHist.NoOfEmailsSent
});
var SOSAndTrackInfo = qryReturn
.GroupBy(qry => new { qry.UserName, qry.MobileNumber })
.Select(g =>
new
{
SNo = Convert.ToString(rowCount++),
UserName = g.Key.UserName,
MobileNumber = g.Key.MobileNumber,
TotalTracks = g.Count(),
TotalSOSs = g.Count(x => x.InSOS.Value),
TotalSMSSent = g.Sum(x => x.NoOfSMSSents != null ? x.NoOfSMSSents : 0) * g.Sum(x => x.NoOfSMSRecipients != null ? x.NoOfSMSRecipients : 0),
TotalEmailSent = g.Sum(x => x.NoOfEmailsSents != null ? x.NoOfEmailsSents : 0) * g.Sum(x => x.NoOfEmailRecipients != null ? x.NoOfEmailRecipients : 0)
});
return(SOSAndTrackInfo.ToList <object>());
}
catch (Exception ex) { return(null); }
}
public async void ShowAverage()
{
TempHistory.Clear();
CloudTable table = App.tableClient.GetTableReference("TempHistory");
var date = DateTime.Now;
var dateOffset = new DateTimeOffset(date.Year, date.Month, date.Day, 0, 0, 0, TimeZoneInfo.Local.BaseUtcOffset.Subtract(TimeSpan.FromHours(1)));
TableQuery <HistoryEntity> query = new TableQuery <HistoryEntity>()
.Where(TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "KeozPi"),
TableOperators.And,
TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.GreaterThan, dateOffset),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.LessThan, dateOffset.AddHours(24))
)
)
);
TableQuerySegment <HistoryEntity> querySegment = null;
while (querySegment == null || querySegment.ContinuationToken != null)
{
querySegment = await table.ExecuteQuerySegmentedAsync(query, querySegment != null?querySegment.ContinuationToken : null);
foreach (var item in querySegment)
{
item.CreatedAtString = item.Timestamp.ToLocalTime().Subtract(TimeSpan.FromHours(1)).ToString("t");
TempHistory.Add(item);
}
}
if (TempHistory.Count > 0)
{
double sum = 0;
foreach (HistoryEntity entity in TempHistory)
{
sum += double.Parse(entity.Temp);
}
sum = sum / TempHistory.Count;
TempNow = String.Format("{0} °C", sum.ToString("##.#"));
}
else
{
TempNow = "Not enough data";
}
TempText = "Average today";
}
/// <summary>
/// Gets all of the Shift Mapping Entities in a Batch manner.
/// </summary>
/// <param name="userModel">The User Model.</param>
/// <param name="monthPartitionKey">Month Partition Value.</param>
/// <param name="queryStartDate">Query start date.</param>
/// <param name="queryEndDate">Query end date.</param>
/// <returns>A unit of execution that contains a list of shift mapping entities.</returns>
private async Task <List <TeamsShiftMappingEntity> > GetAllShiftMappingEntitiesInBatchAsync(
UserDetailsModel userModel,
string monthPartitionKey,
string queryStartDate,
string queryEndDate)
{
if (userModel is null)
{
throw new ArgumentNullException(nameof(userModel));
}
await this.EnsureInitializedAsync().ConfigureAwait(false);
var getEntitiesProps = new Dictionary <string, string>()
{
{ "CurrentCallingAssembly", Assembly.GetCallingAssembly().GetName().Name },
};
this.telemetryClient.TrackTrace(MethodBase.GetCurrentMethod().Name, getEntitiesProps);
CultureInfo culture = CultureInfo.InvariantCulture;
string userFilter = TableQuery.GenerateFilterCondition("KronosPersonNumber", QueryComparisons.Equal, userModel?.KronosPersonNumber);
string monthPartitionFilter = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, monthPartitionKey);
string startDateFilter = TableQuery.GenerateFilterConditionForDate("ShiftStartDate", QueryComparisons.GreaterThanOrEqual, Convert.ToDateTime(queryStartDate, culture));
string endDateFilter = TableQuery.GenerateFilterConditionForDate("ShiftStartDate", QueryComparisons.LessThanOrEqual, Convert.ToDateTime(queryEndDate, culture).AddDays(1));
// Table query
TableQuery <TeamsShiftMappingEntity> query = new TableQuery <TeamsShiftMappingEntity>();
query.Where(TableQuery.CombineFilters(
TableQuery.CombineFilters(startDateFilter, TableOperators.And, endDateFilter),
TableOperators.And,
TableQuery.CombineFilters(monthPartitionFilter, TableOperators.And, userFilter)));
// Results list
var results = new List <TeamsShiftMappingEntity>();
TableContinuationToken continuationToken = null;
do
{
TableQuerySegment <TeamsShiftMappingEntity> queryResults = await this.shiftEntityMappingCloudTable.ExecuteQuerySegmentedAsync(query, continuationToken).ConfigureAwait(false);
continuationToken = queryResults.ContinuationToken;
results.AddRange(queryResults.Results);
}while (continuationToken != null);
return(results);
}
private IEnumerable <ParkingSpotStatusUpdateEntity> GetUnresponsiveSpots()
{
var filter = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForDate(nameof(ParkingSpotStatusUpdateEntity.UpdateTimestamp),
QueryComparisons.LessThanOrEqual, DateTimeOffset.Now.AddMinutes(-1)),
TableOperators.And,
TableQuery.GenerateFilterConditionForBool(nameof(ParkingSpotStatusUpdateEntity.FailedToReply),
QueryComparisons.Equal, false));
var query = new TableQuery <ParkingSpotStatusUpdateEntity>().Where(filter);
var results = _heartbeats.ExecuteQuery(query);
return(results);
}
public IEnumerable <Models.SASSensorTable> Get([FromUri] int during = 30)
{
var storeCS = CloudConfigurationManager.GetSetting("StorageConnectionString");
var storageAccount = CloudStorageAccount.Parse(storeCS);
var tableClient = storageAccount.CreateCloudTableClient();
var sensorReadingTable = tableClient.GetTableReference("SASSensor");
var query = new TableQuery <Models.SASSensorTable>().Where(
TableQuery.GenerateFilterConditionForDate("Timestamp",
QueryComparisons.GreaterThanOrEqual, DateTimeOffset.Now.AddDays(-during))
);
var results = sensorReadingTable.ExecuteQuery(query).
Select((ent => (Models.SASSensorTable)ent)).ToList();
return(results);
}
public async Task <List <T> > RetrieveRecentEntitiesAsync <T>(string tableName) where T : TableEntity, new()
{
CloudTable table = this.tableClient.GetTableReference(tableName);
var entities = new List <T>();
TableQuery <T> query = new TableQuery <T>();
query.FilterString = TableQuery.GenerateFilterConditionForDate("Timestamp", "ge", DateTimeOffset.UtcNow.Subtract(TimeSpan.FromMinutes(5)));
var queryResult = await table.ExecuteQuerySegmentedAsync(query, null);
entities.AddRange(queryResult.Results);
return(entities.OrderBy(i => i.Timestamp).ToList <T>());
}
static void Main(string[] args)
{
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("WADServiceFabricSystemEventTable");
var dateTimeOffset = DateTimeOffset.Parse("2018-06-01T00:00:00.702Z");
var query = new TableQuery().Where(
TableQuery.GenerateFilterConditionForDate("PreciseTimeStamp", QueryComparisons.GreaterThanOrEqual, dateTimeOffset)
);
//var operation = TableOperation.Retrieve("0636632154000000000", "b5d34875-f75c-447b-87a6-311283a907cc___IaaS____gwmje2tb6_0___0000000004295651739");
var result = table.ExecuteQuerySegmentedAsync(query, null).GetAwaiter().GetResult();
}
public IEnumerable <ChallengeEntity> GetPastChallenges()
{
var toReturn = new List <ChallengeEntity>();
var eastern = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTimeOffset.UtcNow, "Eastern Standard Time")
.Date
.AddDays(-1);
var queryTime = new DateTimeOffset(eastern, TimeSpan.FromHours(-4));
var filter = TableQuery.GenerateFilterConditionForDate("Date", QueryComparisons.LessThanOrEqual, queryTime);
var result = ObjectService.GetEntities <ChallengeEntity>(filter);
toReturn.AddRange(result);
return(toReturn);
}
/// <summary>
/// Reads all records for a given account name and time duration condition
/// </summary>
/// <param name="mediaServiceAccountName">Account name to load data for</param>
/// <param name="timeWindowInMinutesToLoadData">How far back to go to load data</param>
/// <returns>List of Media Service call history records</returns>
public async Task <IEnumerable <MediaServiceCallHistoryModel> > ListByMediaServiceAccountNameAsync(string mediaServiceAccountName, int timeWindowInMinutesToLoadData)
{
// Table storage implementation for CosmosDb by default indexes all the fields, this query should be fast.
// If old table storage is used that is build on old Azure Table storage service, this query may result in full table scan and could be very expensive to run.
var rangeQuery =
new TableQuery <MediaServiceCallHistoryModelTableEntity>().Where(
TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition(nameof(MediaServiceCallHistoryModel.MediaServiceAccountName), QueryComparisons.Equal, mediaServiceAccountName),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate(nameof(MediaServiceCallHistoryModel.EventTime), QueryComparisons.GreaterThanOrEqual, DateTime.UtcNow.AddMinutes(-timeWindowInMinutesToLoadData))
)
);
return((await this.tableStorageService.QueryDataAsync(rangeQuery).ConfigureAwait(false)).Select(i => i.GetMediaServiceCallHistoryModel()));
}
private void Map(IEnumerable <Criteria> criterias, List <string> filters)
{
foreach (var criteria in criterias.Safe())
{
if (criteria.Value is int i)
{
filters.Add(TableQuery.GenerateFilterConditionForInt(
criteria.Name,
criteria.Operator.ToAbbreviation(),
i));
}
else if (criteria.Value is double d)
{
filters.Add(TableQuery.GenerateFilterConditionForDouble(
criteria.Name,
criteria.Operator.ToAbbreviation(),
d));
}
else if (criteria.Value is long l)
{
filters.Add(TableQuery.GenerateFilterConditionForLong(
criteria.Name,
criteria.Operator.ToAbbreviation(),
l));
}
else if (criteria.Value is bool b)
{
filters.Add(TableQuery.GenerateFilterConditionForBool(
criteria.Name,
criteria.Operator.ToAbbreviation(),
b));
}
else if (criteria.Value is DateTime dt)
{
filters.Add(TableQuery.GenerateFilterConditionForDate(
criteria.Name,
criteria.Operator.ToAbbreviation(),
dt));
}
else
{
filters.Add(TableQuery.GenerateFilterCondition(
criteria.Name,
criteria.Operator.ToAbbreviation(),
WebUtility.UrlEncode(criteria.Value.As <string>())));
}
}
}
private static async Task <List <Request> > GetRequests(CloudTable cloudTable, string userName, Boolean isAdmin, string isApproved)
{
string idQuery = null;
List <Request> requests = new List <Request>();
if (isAdmin)
{
idQuery = TableQuery.GenerateFilterCondition("approved", QueryComparisons.Equal, "waiting");
}
else
{
if (isApproved.Equals("true"))
{
idQuery = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, userName),
TableOperators.And,
TableQuery.GenerateFilterCondition("approved", QueryComparisons.NotEqual, "waiting"));
string deleteQuery = TableQuery.CombineFilters(
idQuery,
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.LessThanOrEqual, DateTimeOffset.Now.AddDays(-1).Date));
TableQuery <Request> deleteRequestQuery = new TableQuery <Request>().Where(deleteQuery);
TableQuerySegment <Request> queryDelteResult = await cloudTable.ExecuteQuerySegmentedAsync(deleteRequestQuery, null);
foreach (TableEntity entity in queryDelteResult.Results)
{
TableOperation delete = TableOperation.Delete(entity);
await cloudTable.ExecuteAsync(delete);
}
}
else
{
idQuery = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, userName),
TableOperators.And,
TableQuery.GenerateFilterCondition("approved", QueryComparisons.Equal, "waiting"));
}
}
TableQuery <Request> query = new TableQuery <Request>().Where(idQuery);
var results = await cloudTable.ExecuteQuerySegmentedAsync <Request>(query, null);
List <Request> cloudRequest = results.Results;
return(cloudRequest);
}
/// <summary>
/// Refresh node mapping table according to the last node refresh time
/// this is used for SOA proxy to update the node mapping info in timer callback
/// </summary>
public void RefreshNodeMapping()
{
lock (this._operationLock)
{
CloudTable table = this.TableClient.GetTableReference(this.NodeMappingTableName);
TableQuery <NodeMappingTableEntry> tableQuery = new TableQuery <NodeMappingTableEntry>().Where(
TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.GreaterThan, this.lastNodeRefreshDate)
);
TableContinuationToken continuationToken = null;
do
{
// Retrieve a segment (up to 1000 entities)
TableQuerySegment <NodeMappingTableEntry> tableQueryResult = table.ExecuteQuerySegmented(tableQuery, continuationToken);
continuationToken = tableQueryResult.ContinuationToken;
foreach (NodeMappingTableEntry entry in tableQueryResult.Results)
{
Debug.Assert(entry.EndpointString != null);
//Update the cache if the epr in the cache is out-of-date
string physicalEpr = null;
if (this._logicalNameToPhysicalEprMapping.TryGetValue(entry.NodeName, out physicalEpr))
{
if (!string.Equals(physicalEpr, entry.EndpointString, StringComparison.OrdinalIgnoreCase))
{
this._logicalNameToPhysicalEprMapping[entry.NodeName] = entry.EndpointString;
this._logicalNameToPhysicalInstanceMapping[entry.NodeName] = entry.RoleInstanceName;
this._physicalEprToLogicalNameMapping[entry.EndpointString] = entry.NodeName;
}
}
else
{
//insert the node mapping info in the cache
this._logicalNameToPhysicalEprMapping[entry.NodeName] = entry.EndpointString;
this._logicalNameToPhysicalInstanceMapping[entry.NodeName] = entry.RoleInstanceName;
this._physicalEprToLogicalNameMapping[entry.EndpointString] = entry.NodeName;
}
if (entry.Timestamp > this.lastNodeRefreshDate)
{
this.lastNodeRefreshDate = entry.Timestamp;
}
}
} while (continuationToken != null);
this._needRefreshOnNextOperation = false;
}
}
public async void RemoveStaleUrls()
{
// Logically delete urls not accessed for x days (x = 90)
// Move expired urls from the 'True' partition to the 'False' partition
try
{
// Construct the query operation
TableQuery <LittleUrlAzure> query = new TableQuery <LittleUrlAzure>()
.Where(TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, bool.TrueString),
TableOperators.And,
TableQuery.GenerateFilterConditionForDate("LastAccessedTime", QueryComparisons.GreaterThan,
DateTime.UtcNow.AddDays(_retentionDays))));
// Collect the urls in the list
TableContinuationToken token = null;
do
{
TableQuerySegment <LittleUrlAzure> resultSegment =
await _littleUrlTable.ExecuteQuerySegmentedAsync <LittleUrlAzure>(query, token);
token = resultSegment.ContinuationToken;
foreach (LittleUrlAzure entity in resultSegment.Results)
{
// Delete
TableOperation deleteOp = TableOperation.Delete(entity);
await _littleUrlTable.ExecuteAsync(deleteOp);
// Insert into the deleted partition
entity.PartitionKey = bool.FalseString;
entity.DeletedTime = DateTime.UtcNow;
// Set retention limit (midnight 90 days from now)
DateTime purgeDate = DateTime.UtcNow.AddDays(_retentionDays);
entity.PurgeDate = new DateTime(purgeDate.Year, purgeDate.Month, purgeDate.Day,
23, 59, 59, 999, DateTimeKind.Utc);
TableOperation insertOp = TableOperation.Insert(entity);
await _littleUrlTable.ExecuteAsync(insertOp);
}
} while (token != null);
}
catch (Exception ex)
{
Debug.WriteLine("RemoveStaleUrls() | Exception caught:" + ex.Message);
}
}
/// <summary>
/// The retrieve top records.
/// </summary>
/// <param name="tableName">
/// The table name.
/// </param>
/// <typeparam name="T">
/// The table entity
/// </typeparam>
/// <returns>
/// The <see cref="IEnumerable"/>.
/// </returns>
private IEnumerable <T> RetirieveTopRecords <T>(string tableName) where T : ITableEntity, new()
{
string filter = TableQuery.GenerateFilterConditionForDate(
"Timestamp",
QueryComparisons.GreaterThanOrEqual,
DateTimeOffset.UtcNow.AddDays(-1).Date);
TableQuery <T> tableQuery = new TableQuery <T> {
TakeCount = 1000
};
tableQuery.Where(filter);
var table = this.tableClient.GetTableReference(tableName);
return(table.ExecuteQuery(tableQuery).ToList());
}
