/// <summary>
/// The get most visited clusters for the company self, not include competitor.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="count">The count.</param>
/// <returns>IList<System.String>.</returns>
public IList <string> GetMostVisitedClustersSelf(DateTime start, DateTime end, int count)
{
var ids = new List <string>();
using (var context = this.GetContext())
{
var filter = this.currentClientUser.UserFilter;
var pattern = this.indexHelper.BuildPatternFromFilter(filter);
for (var s = start; s <= end; s = s.AddDays(1))
{
var tableName = TableNameHelper.GetNewsStreamTableName(this.currentClientUser.GetProfile().Postfix);
try
{
var cluster = context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})", pattern).ToList();
}
catch (Exception e) { continue; }
var clusters = context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})", pattern).ToList();
var idList = clusters.ToList();
var items =
from vi in context.HostVisitCount
where vi.Date == s && idList.Contains(vi.ClusterId0)
group vi by vi.ClusterId0 into z
select new { id = z.Key, val = z.Sum(d => d.Count) };
var list = items.OrderByDescending(i => i.val).Take(count).ToList();
ids.AddRange(list.Where(id => id != null).Select(x => x.id));
}
return(ids.Distinct().ToList());
}
}
/// <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>
/// Initializes a new instance of the <see cref="WeeklyReportRepository"/> class.
/// </summary>
/// <param name="user">The user.</param>
public WeeklyReportRepository(ClientUser user)
{
this.currentUser = user;
var postfix = this.currentUser.GetProfile().Postfix;
this.locationAndUserDemoTableName = TableNameHelper.GetLocationAndUserDemoTableName(postfix);
this.newsStreamTableName = TableNameHelper.GetNewsStreamTableName(postfix);
this.newsSentimentsTableName = TableNameHelper.GetNewsSentimentTableName(postfix);
this.hostVisitCountTableName = TableNameHelper.GetHostVisitCountTableName(postfix);
}
/// <summary>
/// Generates the location pv.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="companies">The companies.</param>
/// <returns>List<LocationPV>.</returns>
public List <LocationPV> GenerateLocationPv(DateTime start, DateTime end, IEnumerable <string> companies)
{
start = new DateTime(start.Year, start.Month, start.Day);
end = new DateTime(end.Year, end.Month, end.Day);
var result = new List <LocationPV>();
using (var context = this.GetContext())
{
foreach (var company in companies)
{
var location = new LocationPV();
var filter = this.keywordManager.GetFilters(company);
location.Name = filter.UserName;
var pattern = this.indexHelper.BuildPatternFromFilter(filter);
var tableName = TableNameHelper.GetNewsStreamTableName(this.currentClientUser.GetProfile().Postfix);
try
{
var cluster =
context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})",
pattern).ToList();
}catch (Exception e) { continue; }
var clusters =
context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})",
pattern).ToList();
var idList = clusters.ToList();
var vs = from vi in context.HostVisitCount
where vi.Date >= start && vi.Date <= end && idList.Contains(vi.ClusterId0)
group vi by vi.State
into s
select new { state = s.Key, value = s.Sum(d => d.Count) };
var visitCount = vs.ToDictionary(i => i.state);
foreach (var item in visitCount)
{
var detail = new LocationPVDetail {
Name = item.Key, VisitCount = (int)item.Value.value
};
location.Details.Add(detail);
}
result.Add(location);
}
}
return(result);
}
/// <summary>
/// Generates the age distr.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="companies">The companies.</param>
/// <returns>List<AgeDistribution>.</returns>
public List <AgeDistribution> GenerateAgeDistr(DateTime start, DateTime end, IEnumerable <string> companies)
{
start = new DateTime(start.Year, start.Month, start.Day);
end = new DateTime(end.Year, end.Month, end.Day);
var result = new List <AgeDistribution>();
using (var context = this.GetContext())
{
foreach (var company in companies)
{
var ages = new AgeDistribution();
ages.Name = company;
var filter = this.keywordManager.GetFilters(company);
var pattern = this.indexHelper.BuildPatternFromFilter(filter);
var tableName = TableNameHelper.GetNewsStreamTableName(this.currentClientUser.GetProfile().Postfix);
try
{
var cluster =
context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})",
pattern).ToList();
}catch (Exception e) { continue; }
var clusters =
context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})",
pattern).ToList();
var idList = clusters.ToList();
var vs = from vi in context.LocationAndUserDemo
where vi.Date >= start && vi.Date <= end && idList.Contains(vi.ClusterId0)
group vi by vi.AgeGroup
into s
select new { key = s.Key, value = s.Sum(x => x.QueryViewCount) };
var visitCount = vs.ToDictionary(i => i.key);
foreach (var item in visitCount)
{
var detail = new AgeDetail {
Name = item.Key, Value = (int)item.Value.value
};
ages.Details.Add(detail);
}
result.Add(ages);
}
}
return(result);
}
/// <summary>
/// Initializes a new instance of the <see cref="NewsStreamMap"/> class.
/// </summary>
/// <param name="postfix">The postfix.</param>
public NewsStreamMap(string postfix)
{
// Primary Key
this.HasKey(t => new { t.Date, t.Url });
// Properties
this.Property(t => t.Url).IsRequired().HasMaxLength(1024);
this.Property(t => t.Title).HasMaxLength(1024);
this.Property(t => t.Description).HasMaxLength(1024);
this.Property(t => t.NewsArticleCategory).HasMaxLength(255);
this.Property(t => t.NewsSource).HasMaxLength(255);
this.Property(t => t.GoodDominantImageURL).HasMaxLength(1024);
this.Property(t => t.KeyWords).HasMaxLength(255).IsUnicode();
this.Property(t => t.ClusterId0).HasMaxLength(255);
this.Property(t => t.ClusterId1).HasMaxLength(255);
this.Property(t => t.ClusterId2).HasMaxLength(255);
this.Property(t => t.ClusterId3).HasMaxLength(255);
this.Property(t => t.ClusterId4).HasMaxLength(255);
// Table & Column Mappings
this.ToTable(TableNameHelper.GetNewsStreamTableName(postfix));
this.Property(t => t.Date).HasColumnName("Date");
this.Property(t => t.Url).HasColumnName("Url");
this.Property(t => t.Title).HasColumnName("Title");
this.Property(t => t.NewsArticleDescription).HasColumnName("NewsArticleDescription");
this.Property(t => t.Description).HasColumnName("Description");
this.Property(t => t.NewsArticleCategory).HasColumnName("NewsArticleCategory");
this.Property(t => t.NewsSource).HasColumnName("NewsSource");
this.Property(t => t.GoodDominantImageURL).HasColumnName("GoodDominantImageURL");
this.Property(t => t.KeyWords).HasColumnName("KeyWords");
this.Property(t => t.ClusterId0).HasColumnName("ClusterId0");
this.Property(t => t.ClusterId1).HasColumnName("ClusterId1");
this.Property(t => t.ClusterId2).HasColumnName("ClusterId2");
this.Property(t => t.ClusterId3).HasColumnName("ClusterId3");
this.Property(t => t.ClusterId4).HasColumnName("ClusterId4");
this.Property(t => t.Id).HasColumnName("Id");
this.Property(t => t.BuildTime).HasColumnName("BuildTime");
}
/// <summary>
/// The generate media exposure.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="companies">The companies.</param>
/// <returns>The <see cref="List" />.</returns>
public List <MediaExposure> GenerateMediaExposure(DateTime start, DateTime end, IEnumerable <string> companies)
{
start = new DateTime(start.Year, start.Month, start.Day);
end = new DateTime(end.Year, end.Month, end.Day);
var result = new List <MediaExposure>();
using (var context = this.GetContext())
{
foreach (var company in companies)
{
var filter = this.keywordManager.GetFilters(company);
var pattern = this.indexHelper.BuildPatternFromFilter(filter);
var exposure = new MediaExposure();
exposure.Name = company;
var tableName = TableNameHelper.GetNewsStreamTableName(this.currentClientUser.GetProfile().Postfix);
try
{
var cluster =
context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})",
pattern).ToList();
}catch (Exception e) { continue; }
var clusters =
context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})",
pattern).ToList();
var idList = clusters.ToList();
var vs = from vi in context.HostVisitCount
where vi.Date >= start && vi.Date <= end && idList.Contains(vi.ClusterId0)
group vi by vi.Date
into s
select new { date = s.Key, value = s.Sum(d => d.Count) };
var visitCount = vs.ToDictionary(i => i.date);
var rs =
context.Database.SqlQuery <DateKeyVal>(
$@"select [date] as [key], count(*) as val from {tableName}
where date >= {{0}} and date <={{1}} and Contains(keywords, {{2}})
group by date",
start,
end,
pattern).ToList();
var reportCount = rs.ToDictionary(i => i.Key);
for (var i = start; i < end; i = i.AddDays(1))
{
var d = new MediaExposureDetail {
Date = i.ToShortDateString()
};
if (visitCount.ContainsKey(i))
{
d.VisitCount = (int)visitCount[i].value;
}
if (reportCount.ContainsKey(i))
{
d.ReportCount = reportCount[i].Val;
}
exposure.Details.Add(d);
}
result.Add(exposure);
}
}
return(result);
}