/// <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 news stream for sentiment.
/// </summary>
/// <param name="keyword">The keyword.</param>
/// <param name="takeCount">The take count.</param>
/// <param name="sentimentType">Type of the sentiment.</param>
/// <returns>IEnumerable<NewsStream>.</returns>
public IEnumerable <NewsStream> GetNewsStreamForSentiment(string keyword, int takeCount, string sentimentType)
{
var tableNameSentiment = TableNameHelper.GetSentimentResultNewsTableName(this.currentUser.Postfix);
var tableNameNewsStream = this.GetNewsHourlyTableName();
var filterClause = this.GetFilterClauseBySentimentType(sentimentType, SENTIMENTSCORECOLUMNNAME);
var filterClauseOrdery = this.GetFilterClauseBySentimentOrderBy(sentimentType, SENTIMENTSCORECOLUMNNAME);
var filter = new List <string>();
var keyfilter = "";
foreach (var uf in this.currentUser.UserFilter.UserFilterListCollection)
{
foreach (var keyw in uf.Filters.ToList())
{
filter.Add($"Contains(KeyWords,\'{keyw.Value}\')");
}
}
keyfilter = string.Join(" or ", filter);
string sql =
$"select Top {takeCount} * from (select * from {tableNameNewsStream} where {keyfilter})as a join (select * from {tableNameSentiment} where Date = (select max(Date) from {tableNameSentiment} where Name = N'{keyword}') and {filterClause}) as b on a.Id=b.Id {filterClauseOrdery}";
return(this.dbUtilities.ExecuteStoreQuery <NewsStream>(this.Context, sql));
}