/// <summary>Gets the afk all core.</summary>
/// <returns>
/// Return All AFK bot-wide (un-cached)
/// </returns>
public async Task <IEnumerable <Afk> > GetAfkAllCore()
{
var data = await _queryService
.CreateMySqlFactory()
.FromTable(BaseTable)
.GetAsync <Afk>();
return(data);
}
public bool IsExist(LearnData learnData)
{
var select = _queryService
.CreateMySqlFactory()
.FromTable(TableName)
.Where("message", learnData.Message)
.Get();
return(select.Any());
}
public async Task <string> GetCurrentDbName()
{
var query = await _queryService
.CreateMySqlFactory()
.RunSqlQueryAsync <string>("select database() as current_db_name");
var dbName = query.FirstOrDefault("db_backup");
return(dbName);
}
public async Task <IEnumerable <CloudTag> > GetTagsByGroupCoreAsync(long chatId)
{
var data = await _queryService
.CreateMySqlFactory()
.FromTable(TableName)
.Where("chat_id", chatId)
.OrderBy("tag")
.GetAsync <CloudTag>();
return(data.Where(tag => tag.Tag.WordsCount() == 1));
}
public async Task <IEnumerable <MessageHistory> > GetMessageHistoryAsync(MessageHistoryFindDto findDto)
{
var where = findDto.ToDictionary();
var query = await _queryService
.CreateMySqlFactory()
.FromTable(TableName)
.Where(where)
.GetAsync <MessageHistory>();
return(query);
}
public async Task <bool> IsExist(
string key,
string value
)
{
var query = await _queryService
.CreateMySqlFactory()
.FromTable(baseTable)
.Where(key, value)
.GetAsync();
return(query.Any());
}
public async Task <bool> IsExistAsync(Dictionary <string, object> where)
{
var check = await _queryService
.CreateMySqlFactory()
.FromTable(TableName)
.Where(where)
.GetAsync();
var isExist = check.Any();
Log.Debug("Group setting IsExist: {IsExist}", isExist);
return(isExist);
}
public async Task SaveBlockList(BlockList data)
{
await _queryService
.CreateMySqlFactory()
.FromTable(TableName)
.InsertAsync(data);
}
public async Task <IEnumerable <CloudTag> > GetNotesByChatId(long chatId)
{
var data = await _cacheService.GetOrSetAsync(
cacheKey : "notes" + chatId.ReduceChatId(),
action : () => {
var query = _queryService
.CreateMySqlFactory()
.FromTable(TableName)
.Where("chat_id", chatId)
.OrderBy("tag")
.GetAsync <CloudTag>();
return(query);
}
);
return(data);
}
public async Task <ChatSetting> GetSettingsByGroupCore(long chatId)
{
var where = new Dictionary <string, object>
{
{ "chat_id", chatId }
};
var data = await _queryService
.CreateMySqlFactory()
.FromTable(BaseTable)
.Where(where)
.FirstOrDefaultAsync <ChatSetting>();
return(data);
}
/// <summary>
/// Hangfire storage reset
/// </summary>
public async Task ResetHangfireMySqlStorage(ResetTableMode resetTableMode = ResetTableMode.Truncate)
{
if (_hangfireConfig.DataStore != HangfireDataStore.MySql)
{
Log.Information("Reset Hangfire MySQL Storage isn't required because Hangfire DataStore is {HangfireDataStore}", _hangfireConfig.DataStore);
return;
}
Log.Information("Starting reset Hangfire MySQL storage");
const string prefixTable = "_hangfire";
var sbSql = new StringBuilder();
var listTable = new[]
{
"AggregatedCounter",
"Counter",
"DistributedLock",
"Hash",
"JobParameter",
"JobQueue",
"State",
"List",
"Server",
"Set",
"State",
"Job"
};
sbSql.AppendLine("SET FOREIGN_KEY_CHECKS = 0;");
foreach (var table in listTable)
{
var tableName = $"{prefixTable}{table}";
var resetMode = resetTableMode.Humanize().ToUpperCase();
sbSql.Append(resetMode).Append(" TABLE ");
if (resetMode.Contains("drop", StringComparison.CurrentCultureIgnoreCase))
{
sbSql.Append("IF EXISTS ");
}
sbSql.Append(tableName).AppendLine(";");
}
sbSql.AppendLine("SET FOREIGN_KEY_CHECKS = 1;");
var sqlTruncate = sbSql.ToTrimmedString();
var rowCount = await _queryService
.CreateMySqlFactory()
.RunSqlAsync(sqlTruncate);
Log.Information("Reset Hangfire MySQL storage finish. Result: {RowCount}", rowCount);
}
public async Task FlushHitActivity()
{
Log.Information("Flushing HitActivity buffer");
var metrics = LiteDbProvider.GetCollections <HitActivity>();
var dateFormat = "yyyy-MM-dd HH";
var dateFormatted = DateTime.Now.ToString(dateFormat);
Log.Debug("Filter last hour: {DateFormatted}", dateFormatted);
var filteredMetrics = metrics.Find(x =>
x.Timestamp.ToString(dateFormat) == dateFormatted).ToList();
if (filteredMetrics.Count == 0)
{
Log.Debug("No HitActivity buffed need to flush");
return;
}
Log.Debug("Flushing {Count} of {CountAll} data..", filteredMetrics.Count, metrics.Count());
foreach (var hitActivity in filteredMetrics)
{
var data = new Dictionary <string, object>()
{
{ "via_bot", hitActivity.ViaBot },
{ "update_type", hitActivity.UpdateType },
{ "from_id", hitActivity.FromId },
{ "from_first_name", hitActivity.FromFirstName },
{ "from_last_name", hitActivity.FromLastName },
{ "from_username", hitActivity.FromUsername },
{ "from_lang_code", hitActivity.FromLangCode },
{ "chat_id", hitActivity.ChatId },
{ "chat_username", hitActivity.ChatUsername },
{ "chat_type", hitActivity.ChatType },
{ "chat_title", hitActivity.ChatTitle },
{ "timestamp", hitActivity.Timestamp }
};
var insertHit = await _queryService
.CreateMySqlFactory()
.FromTable("hit_activity")
.InsertAsync(data);
Log.Information("Insert Hit: {InsertHit}", insertHit);
}
Log.Debug("Clearing local data..");
filteredMetrics.ForEach(x => {
metrics.DeleteMany(y => y.Timestamp == x.Timestamp);
});
LiteDbProvider.Rebuild();
Log.Information("Flush HitActivity done");
}
/// <summary>
/// Saves the ban async.
/// </summary>
/// <param name="globalBanItem">The global ban data.</param>
/// <returns>A Task.</returns>
public async Task <bool> SaveBanAsync(GlobalBanItem globalBanItem)
{
var userId = globalBanItem.UserId;
var fromId = globalBanItem.BannedBy;
var chatId = globalBanItem.BannedFrom;
var reason = globalBanItem.ReasonBan;
var data = new Dictionary <string, object>()
{
{ "user_id", userId },
{ "from_id", fromId },
{ "chat_id", chatId },
{ "reason", reason }
};
Log.Information("Inserting new GBan: {@V}", globalBanItem);
var query = await _queryService
.CreateMySqlFactory()
.FromTable(GBanTable)
.InsertAsync(data);
return(query > 0);
}
public async Task <JsonResult> TopActivityLast30Days()
{
var stopwatch = Stopwatch.StartNew();
var model = await _queryService
.CreateMySqlFactory()
.FromTable("zizibot_data.view_top_hit_activity_last_30d")
.GetAsync <TopTrendActivity>();
var topTrendActivities = model.ToList();
stopwatch.Stop();
Log.Information("Elapsed. {Elapsed}", stopwatch.Elapsed);
return(Json(new
{
Data = topTrendActivities,
Count = topTrendActivities.Count
}));
}
public async Task <bool> IsHistoryExist(
long chatId,
string url
)
{
var data = await _queryService
.CreateMySqlFactory()
.FromTable(RSSHistoryTable)
.Where("chat_id", chatId)
.Where("url", url)
.GetAsync <RssHistory>();
var isExist = data.Any();
Log.Debug(
"Check RSS History exist on ChatId {ChatId}? {IsExist}",
chatId,
isExist
);
return(isExist);
}
