/// <summary>
/// Gets the news stream.
/// </summary>
/// <param name="filterWords">The filter words.</param>
/// <param name="number">The number.</param>
/// <param name="userName">Name of the user.</param>
/// <returns>IEnumerable<NewsStream>.</returns>
public IEnumerable <NewsStream> GetNewsStream(List <string> filterWords, int number, ClientUser user)
{
var hourlyNewsTableName = this.GetNewsHourlyTableName();
var wordCloudTableName = TableNameHelper.GetWordCloudTableName(user.Postfix);
var sb = new StringBuilder();
if (filterWords != null && filterWords.Count > 0)
{
foreach (var keyWord in filterWords)
{
sb.Append($"KeyWords like N'%{keyWord}%'");
sb.Append(" and ");
}
sb.Remove(sb.Length - 4, 4);
}
var word = filterWords.Count > 0 ? filterWords.First() : string.Empty;
string sql =
$"select top {number} * from {hourlyNewsTableName} where Date >= dateadd(Day, -1, (select max(Date) from {wordCloudTableName} where Word=N'{word}')) ";
if (sb.Length > 0)
{
sql += $" and {sb}";
}
return(this.dbUtilities.ExecuteStoreQuery <NewsStream>(this.Context, sql));
}
/// <summary>
/// Clears the user tables.
/// </summary>
/// <param name="postfix">The postfix.</param>
public void ClearUserTables(string postfix)
{
var tableList = new List <string>
{
TableNameHelper.GetHostVisitCountTableName(postfix),
TableNameHelper.GetHostVisitCountHourlyTableName(postfix),
TableNameHelper.GetLocationAndUserDemoTableName(postfix),
TableNameHelper.GetNewsHourlyTableName(postfix),
TableNameHelper.GetNewsSentimentTableName(postfix),
TableNameHelper.GetNewsStreamTableName(postfix),
TableNameHelper.GetSentimentResultNewsTableName(postfix),
TableNameHelper.GetSentimentResultTableName(postfix),
TableNameHelper.GetWordCloudTableName(postfix)
};
using (var db = ContextFactory.GetProfileContext())
{
var sb = new StringBuilder();
foreach (var table in tableList)
{
sb.AppendLine($"Truncate Table {table};");
}
db.Database.CommandTimeout = 300;
try
{
db.Database.ExecuteSqlCommand(sb.ToString());
}catch (Exception e)
{
}
}
repository.CleanUserDataLoadHistory(postfix);
}
/// <summary>
/// Gets the word cloud records.
/// </summary>
/// <param name="userName">Name of the user.</param>
/// <param name="key">The key.</param>
/// <returns>IEnumerable<WordCloudItem>.</returns>
public IEnumerable <WordCloudItem> GetWordCloudRecords(ClientUser user, string key)
{
var wordCloudTable = TableNameHelper.GetWordCloudTableName(user.Postfix);
var sql =
$"select Word, RelatedWords from {wordCloudTable} where Date = (select max(Date) from {wordCloudTable}) and Word = N'{key}'";
return(this.dbUtilities.ExecuteStoreQuery <WordCloudItem>(this.Context, sql));
}