/// <summary>
/// Filters from post.
/// </summary>
/// <param name="obj">The object.</param>
/// <returns>CustomerFilters.</returns>
private CustomerFilters FilterFromPost(dynamic obj)
{
var result = new CustomerFilters();
result.UserName = obj.name;
result.UserFilterListCollection.Clear();
foreach (var filter in obj.filters)
{
var list = new FilterList();
var array = ((string)filter).Split(new[] { '+' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var val in array)
{
if (!string.IsNullOrEmpty(val))
{
list.Filters.Add(new Filter {
Name = "Keywords", Value = val
});
}
}
if (list.Filters.Count > 0)
{
result.UserFilterListCollection.Add(list);
}
}
return(result);
}
/// <summary>
/// Gets the visit gender count.
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="analysisTime">The analysis time.</param>
/// <returns>IEnumerable<NameCount>.</returns>
public IEnumerable <NameCount> GetVisitGenderCount(CustomerFilters filter, DateTime analysisTime)
{
using (var context = ContextFactory.GetContext(this.currentUser.GetProfile()))
{
var keyword = this.indexHelper.BuildPatternFromFilter(filter);
var startTime = analysisTime - TimeSpan.FromDays(7);
var endTime = analysisTime;
string sqlCmd =
$@"DECLARE @@FilterTable table (
[ClusterId0][nvarchar](255) NULL
)
INSERT @@FilterTable
select distinct[ClusterId0] from[dbo].[{this
.newsStreamTableName}]
where contains(keywords, {{0}})
and date between {{1}} and {{2}}
SELECT gender as Name, count(*) as Count
from
(
select * from [dbo].[{this
.locationAndUserDemoTableName}]
where ClusterId0 in (SELECT ClusterId0 from @@FilterTable)
) T
group by gender";
object[] parameters = { keyword, startTime, endTime };
var result = ExecuteStoreQuery <NameCount>(context, sqlCmd, parameters);
return(result);
}
}
public Task <List <T> > GetForOrganization <T>(Guid organizationId, CustomerFilters filters) where T : OrganizationCustomerOutput
{
return(Repository.Queryable()
.ApplyWhereFilters(filters)
.Where(x => x.OrganizationId == organizationId)
.ProjectTo <T>(ProjectionMapping)
.ToListAsync());
}
public Task <PackedList <T> > GetForOrganization <T>(Guid organizationId,
CustomerFilters filters, CommonFilters pagingFilters) where T : OrganizationCustomerOutput
{
return(Repository.Queryable()
.ApplyWhereFilters(filters)
.Where(x => x.OrganizationId == organizationId)
.PaginateProjection <OrganizationCustomer, T>(pagingFilters, ProjectionMapping));
}
public IQueryable <Customer> Search(IQueryable <Customer> query, CustomerFilters filters)
{
if (!string.IsNullOrWhiteSpace(filters.Name))
{
query = query.Where(o => o.CusName.Contains(filters.Name));
}
return(query);
}
public CustomerFilters GetFilters()
{
var filters = new CustomerFilters()
{
Page = Convert.ToInt32(HttpContext.Request.Query["page"]),
Limit = Convert.ToInt32(HttpContext.Request.Query["limit"]),
Name = HttpContext.Request.Query["name"],
Code = HttpContext.Request.Query["code"],
};
return(filters);
}
/// <summary>
/// Gets the visit count trend.
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="AnalysisTime">The analysis time.</param>
/// <returns>IEnumerable<TimeCount>.</returns>
public IEnumerable <TimeCount> GetVisitCountTrend(CustomerFilters filter, DateTime AnalysisTime)
{
using (var context = ContextFactory.GetContext(this.currentUser.GetProfile()))
{
var keyword = this.indexHelper.BuildPatternFromFilter(filter);
var startTime = AnalysisTime - TimeSpan.FromDays(7);
var endTime = AnalysisTime;
string sqlCmd =
$@"DECLARE @@FilterTable table
(
[ClusterId0][nvarchar](255) NULL
)
INSERT @@FilterTable
select distinct[ClusterId0] from [dbo].[{this
.newsStreamTableName}]
where contains(keywords, {{0}})
and date between {{1}} and {{2}}
SELECT Date, count(*) as Count
from
(
select * from [dbo].[{this
.hostVisitCountTableName}]
where ClusterId0 in (SELECT ClusterId0 from @@FilterTable)
and date between {{1}} and {{2}}
) T
group by Date";
object[] parameters = { keyword, startTime, endTime };
var DayHourCount = ExecuteStoreQuery <DayHourCount>(context, sqlCmd, parameters);
var result = new List <TimeCount>();
for (var daySequence = 6; daySequence >= 0; daySequence--)
{
var datetime = new DateTime(AnalysisTime.Year, AnalysisTime.Month, AnalysisTime.Day, 0, 0, 0)
- TimeSpan.FromDays(daySequence);
var Count = 0;
foreach (var DBItem in DayHourCount)
{
if (DBItem.date == datetime)
{
Count = DBItem.Count;
break;
}
}
result.Add(new TimeCount(datetime.Month + "/" + datetime.Day, Count));
}
return(result);
}
}
/// <summary>
/// Gets a list of customers by a specified postalcode or city name
/// </summary>
/// <param name="postal">The specified postal code</param>
/// <param name="city">The specified city name</param>
/// <returns>A list of custuomers</returns>
public async Task <List <Customer> > getListByPostalName(int postal, string city)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].[Customer] as c " +
"INNER JOIN dbo.CustomerFilters AS filters ON filters.customerId = c.id " +
"INNER JOIN dbo.CustomerType AS cusType ON cusType.Id = c.CusTypeId " +
"WHERE c.CusPostal = @postal OR c.CusCity = @city", connection);
cmd.Parameters.AddWithValue("@postal", SqlDbType.Int).Value = postal;
cmd.Parameters.AddWithValue("@city", SqlDbType.VarChar).Value = city;
await connection.OpenAsync();
var reader = await cmd.ExecuteReaderAsync();
List <Customer> customerList = new List <Customer>();
CustomerFilters filter = new CustomerFilters();
while (reader.Read())
{
//Gets the filters for customer
filter = new CustomerFilters()
{
Id = reader.GetInt32(9),
Gambling = reader.GetBoolean(11),
Dancing = reader.GetBoolean(12),
Sleep = reader.GetBoolean(13),
Food = reader.GetBoolean(14)
};
//Gets the customer
Customer cus = new Customer()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Image = reader.GetString(2),
City = reader.GetString(3),
Address = reader.GetString(4),
Postal = reader.GetInt32(5),
Phone = reader.GetString(6),
Email = reader.GetString(7),
Filters = new CustomerFilters(),
Type = reader.GetString(16)
};
//Adds the filters to the customer
cus.Filters = filter;
//Adds the customer to the list
customerList.Add(cus);
}
return(customerList);
}
}
/// <summary>
/// Initializes the customer filter.
/// </summary>
/// <param name="userName">Name of the user.</param>
/// <param name="value">The value.</param>
/// <returns>CustomerFilters.</returns>
internal static CustomerFilters InitCustomerFilter(string userName, string value)
{
var filter = new CustomerFilters {
UserName = userName, UserFilterListCollection = new List <FilterList>()
};
var list = new FilterList();
list.Filters.Add(new Filter {
Name = "Keywords", Value = value
});
filter.UserFilterListCollection.Add(list);
return(filter);
}
/// <summary>
/// Gets all customers
/// </summary>
/// <returns>A list with all customers</returns>
public async Task <List <Customer> > GetAllAsync()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].[Customer] as c " +
"INNER JOIN dbo.CustomerFilters AS filters ON filters.customerId = c.id " +
"INNER JOIN dbo.CustomerType AS cusType ON cusType.Id = c.CusTypeId", connection);
await connection.OpenAsync();
var reader = await cmd.ExecuteReaderAsync();
List <Customer> customersList = new List <Customer>();
CustomerFilters filter = new CustomerFilters();
//Gets the customer
while (reader.Read())
{
//Gets the customer filters.
filter = new CustomerFilters()
{
Id = reader.GetInt32(9),
Gambling = reader.GetBoolean(11),
Dancing = reader.GetBoolean(12),
Sleep = reader.GetBoolean(13),
Food = reader.GetBoolean(14)
};
//Gets a customer
Customer cus = new Customer()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Image = reader.GetString(2),
City = reader.GetString(3),
Address = reader.GetString(4),
Postal = reader.GetInt32(5),
Phone = reader.GetString(6),
Email = reader.GetString(7),
Filters = new CustomerFilters(),
Type = reader.GetString(16)
};
//Adds the filters to the customer
cus.Filters = filter;
//Add the customer to the list
customersList.Add(cus);
}
return(customersList);
}
}
/// <summary>
/// Generates the indexed result.
/// </summary>
/// <param name="userid">The userid.</param>
/// <param name="userFilter">The user filter.</param>
public void GenerateIndexedResult(ClientUser user, CustomerFilters userFilter)
{
this.newDate = this.repository.GetNewDate(user.Postfix);
if (this.newDate != DateTime.MinValue)
{
this.BuildHashTable(user.Postfix, userFilter);
var indexedResult = this.GenerateIndexedData();
var sentiResult = this.GenerateSentimentResult(user.Name);
this.repository.UpdateSentiTable(user.Postfix, sentiResult);
this.repository.UpdateWordCloudsTable(user.Postfix, indexedResult);
var sentiResultNews = this.GenerateSentimentResultNews(user.Name);
this.repository.UpdateSentiNewsTable(user.Postfix, sentiResultNews);
}
}
public async Task <ResponseModel <CustomerViewModel> > GetList(CustomerFilters filters, SortCol sortCol
, AccountViewModel account)
{
var query = _context.Customer.Where(o => o.CompanyId == account.CompanyId && o.IsDelete == 0);
query = Search(query, filters);
query = Sort(query, sortCol);
var vms = await query.Skip((filters.Page - 1) *filters.Limit).Take(filters.Limit)
.ProjectTo <CustomerViewModel>().ToListAsync();
ResponseModel <CustomerViewModel> result = new ResponseModel <CustomerViewModel>();
result.status = 0;
result.message = "";
result.total = query.Count();
result.data = vms;
return(result);
}
/// <summary>
/// Loads the user data.
/// </summary>
/// <param name="postfix">The postfix.</param>
/// <param name="userFilter">The user filter.</param>
/// <param name="CompetitorFilter">The competitor filter.</param>
public void LoadUserData(string postfix, CustomerFilters userFilter, List <CustomerFilters> CompetitorFilter)
{
var filter = new List <string>();
var allfilter = "";
var keywords = this.indexHelper.BuildPatternFromFilter(userFilter);
filter.Add($"Contains(KeyWords,\'{keywords}\')");
foreach (var uf in CompetitorFilter)
{
keywords = this.indexHelper.BuildPatternFromFilter(uf);
filter.Add($"Contains(KeyWords,\'{keywords}\')");
}
allfilter = string.Join(" or ", filter);
if (allfilter != "")
{
this.repository.UpdateUserTables(postfix, allfilter);
}
}
/// <summary>
/// Gets the news stream based on source.
/// </summary>
/// <param name="newsSourceList">The news source list.</param>
/// <param name="take">The take.</param>
/// <param name="startTime">The start time.</param>
/// <param name="endTime">The end time.</param>
/// <param name="filter">The filter.</param>
/// <returns>IEnumerable<NewsStream>.</returns>
public IEnumerable <NewsStream> GetNewsStreamBasedOnSource(
List <string> newsSourceList,
int take,
DateTime startTime,
DateTime endTime,
CustomerFilters filter)
{
var pattern = this.indexHelper.BuildPatternFromFilter(filter);
var tableName = this.GetNewsHourlyTableName();
var sb = new StringBuilder();
sb.Append("N'");
sb.Append(string.Join("',N'", newsSourceList));
sb.Append("'");
var sql =
$"select * from (select * , Rank() over(Partition BY NewsSource ORDER BY BuildTime desc, ClusterId0 desc) as Rank from {tableName} where BuildTime >='{startTime}' and BuildTime <='{endTime}' and Contains(Keywords, N'{pattern}') and NewsSource in ({sb})) as a where a.Rank <={take}";
return(this.dbUtilities.ExecuteStoreQuery <NewsStream>(this.Context, sql));
}
/// <summary>
/// Gets the top news list by report count.
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="AnalysisTime">The analysis time.</param>
/// <param name="take">The take.</param>
/// <returns>IEnumerable<NewsBrief>.</returns>
public IEnumerable <NewsBrief> GetTopNewsListByReportCount(
CustomerFilters filter,
DateTime AnalysisTime,
int take)
{
using (var context = ContextFactory.GetContext(this.currentUser.GetProfile()))
{
var keyword = this.indexHelper.BuildPatternFromFilter(filter);
//取出每天报导量最大的N条记录
var startTime = AnalysisTime - TimeSpan.FromDays(7);
var endTime = AnalysisTime;
string sqlCmd =
$@"select convert(nvarchar(max),buildtime,111) as date, [ClusterId0], count(*) as count, min(BuildTime) as MinBuildTime into #FilterTable
from [dbo].[{this
.newsStreamTableName}]
where contains(keywords, {{0}})
and date between {{1}} and {{2}}
group by convert(nvarchar(max),buildtime,111), clusterid0
select date,ClusterId0,MinBuildTime into #TopTable from
(select * , Rank() over(Partition BY Date ORDER BY count desc,clusterid0) as Rank from #FilterTable )
as a where a.Rank <={take}
select T1.Title,T1.Description,T1.GoodDominantImageURL as ThumbnailUrl,T1.NewsSource as Source,T1.Url,T1.ClusterId0,T1.buildtime as CreatedTime from [dbo].[{this
.newsStreamTableName}] T1
inner join #TopTable T2 on T1.ClusterId0=T2.ClusterId0 and T1.BuildTime = T2.MinBuildTime
order by T1.BuildTime desc
drop table #FilterTable
drop table #TopTable ";
object[] parameters = { keyword, startTime, endTime };
var result = ExecuteStoreQuery <NewsBrief>(context, sqlCmd, parameters);
return(result);
}
}
/// <summary>
/// Gets the weekly report count by hour.
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="AnalysisTime">The analysis time.</param>
/// <returns>IEnumerable<DayHourCount>.</returns>
public IEnumerable <DayHourCount> GetWeeklyReportCountByHour(CustomerFilters filter, DateTime AnalysisTime)
{
using (var context = ContextFactory.GetContext(this.currentUser.GetProfile()))
{
var keyword = this.indexHelper.BuildPatternFromFilter(filter);
var list = new List <NameCount>();
var startTime = AnalysisTime - TimeSpan.FromDays(7);
var endTime = AnalysisTime;
string sqlCmd =
$@"DECLARE @@FilterTable table
(
[ClusterId0][nvarchar](255) NULL
)
INSERT @@FilterTable
select distinct[ClusterId0] from[dbo].[{this
.newsStreamTableName}]
where contains(keywords, {{0}})
and date between {{1}} and {{2}}
SELECT date, hour, count(*) as Count
from
(
select date, datepart(hour,buildtime) as hour
from [dbo].[{this
.newsStreamTableName}]
where ClusterId0 in (SELECT ClusterId0 from @@FilterTable)
)T
group by date, hour
order by date, hour ";
object[] parameters = { keyword, startTime, endTime };
var result = ExecuteStoreQuery <DayHourCount>(context, sqlCmd, parameters);
return(result);
}
}
/// <summary>
/// Builds the pattern from filter.
/// </summary>
/// <param name="filter">The filter.</param>
/// <returns>System.String.</returns>
public string BuildPatternFromFilter(CustomerFilters filter)
{
var result = string.Join(" or ", filter.UserFilterListCollection.Select(this.BuildPatternForFilterList));
return(result);
}
public static Expression <Func <OrganizationCustomer, bool> > WhereFilter(CustomerFilters filters)
{
var expr = PredicateBuilder.True <OrganizationCustomer>();
return(expr);
}
/// <summary>
/// Gets the news report count.
/// </summary>
/// <param name="startTime">The start time.</param>
/// <param name="endTime">The end time.</param>
/// <param name="filter">The filter.</param>
/// <returns>IEnumerable<System.Int32>.</returns>
public IEnumerable <int> GetNewsReportCount(DateTime startTime, DateTime endTime, CustomerFilters filter)
{
var pattern = this.indexHelper.BuildPatternFromFilter(filter);
var tableName = this.GetNewsHourlyTableName();
string sql =
$"select Count(*) from {tableName} where date='{startTime}' and Contains(KeyWords, N'{pattern}' ) ";
return(this.dbUtilities.ExecuteStoreQuery <int>(this.Context, sql));
}
/// <summary>
/// Gets the sentiments.
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="AnalysisTime">The analysis time.</param>
/// <returns>IEnumerable<Sentiment>.</returns>
public IEnumerable <Sentiment> GetSentiments(CustomerFilters filter, DateTime AnalysisTime)
{
using (var context = ContextFactory.GetContext(this.currentUser.GetProfile()))
{
var keyword = this.indexHelper.BuildPatternFromFilter(filter);
var list = new List <NameCount>();
var startTime = AnalysisTime - TimeSpan.FromDays(7);
var endTime = AnalysisTime;
string sqlCmd =
$@"DECLARE @@FilterTable table
(
[ClusterId0][nvarchar](255) NULL
)
INSERT @@FilterTable
select distinct[ClusterId0] from[dbo].[{this
.newsStreamTableName}]
where contains(keywords, {{0}})
and date between {{1}} and {{2}}
SELECT date, attitude, count(*) as Count
from
(
select date, attitude
from [dbo].[{this
.newsSentimentsTableName}]
where ClusterId0 in (SELECT ClusterId0 from @@FilterTable)
)T
group by date, attitude
order by date, attitude";
object[] parameters = { keyword, startTime, endTime };
var dataresult = ExecuteStoreQuery <SentimentRowData>(context, sqlCmd, parameters);
var result = new List <Sentiment>();
for (var daySequence = 6; daySequence >= 0; daySequence--)
{
var datetime = new DateTime(AnalysisTime.Year, AnalysisTime.Month, AnalysisTime.Day, 0, 0, 0)
- TimeSpan.FromDays(daySequence);
var Positive = 0;
var Negative = 0;
foreach (var DBItem in dataresult)
{
if (DBItem.Date == datetime)
{
if (DBItem.Attitude.Equals("Negative", StringComparison.InvariantCultureIgnoreCase))
{
Negative = DBItem.Count;
}
else if (DBItem.Attitude.Equals("Positive", StringComparison.InvariantCultureIgnoreCase))
{
Positive = DBItem.Count;
}
}
}
result.Add(new Sentiment(datetime.Month + "/" + datetime.Day, Positive, Negative));
}
return(result);
}
}
public void SaveCustomerFilters(CustomerFilters item)
{
Session["__CustomerFilters"] = item;
}
/// <summary>
/// Gets the news list and news sentiments.
/// </summary>
/// <param name="filter">The filter.</param>
/// <param name="AnalysisTime">The analysis time.</param>
/// <returns>List<NewsBrief>.</returns>
public List <NewsBrief> GetNewsListAndNewsSentiments(CustomerFilters filter, DateTime AnalysisTime)
{
using (var context = ContextFactory.GetContext(this.currentUser.GetProfile()))
{
var keyword = this.indexHelper.BuildPatternFromFilter(filter);
var startTime = AnalysisTime - TimeSpan.FromDays(7);
var endTime = AnalysisTime;
// Only GetNegative
string sqlCmd =
$@"select ClusterId0,BuildTime,Url,Title into #FilterTable from [dbo].[{this.newsStreamTableName}]
where contains(keywords,{{0}}) and
date between {{1}} and {{2}}
SELECT * INTO #SentimentTable
from
(
select *
from [dbo].[{this
.newsSentimentsTableName}]
where ClusterId0 in (SELECT ClusterId0 from #FilterTable)
) T
order by vote desc
SELECT T1.BuildTime as NewsDate, T1.Url, T1.Title, T2.Attitude, T2.Content, T2.Vote, T2.Date
FROM
#FilterTable T1 inner join #SentimentTable T2
ON T1.ClusterId0=T2.ClusterId0
delete #FilterTable WHERE exists
(select * from #FilterTable T2 where T2.ClusterId0 = #FilterTable.ClusterId0 )
drop table #FilterTable
drop table #SentimentTable";
object[] parameters = { keyword, startTime, endTime };
var dataresult = ExecuteStoreQuery <SentimentRowData>(context, sqlCmd, parameters);
var result = new List <NewsBrief>();
foreach (var dbrow in dataresult)
{
//if two comments is to the same news, only keep one
var samenews = false;
foreach (var news in result)
{
if (dbrow.Title.Equals(news.Title, StringComparison.InvariantCultureIgnoreCase))
{
samenews = true;
break;
}
}
if (samenews)
{
continue;
}
var item = new NewsBrief
{
CreatedTime = dbrow.NewsDate,
Url = dbrow.Url,
Title = dbrow.Title,
PositiveComments = new List <CommentsSentiments>(),
NegativeComments = new List <CommentsSentiments>()
};
var sentiments = new CommentsSentiments
{
Attitute = dbrow.Attitude,
Content = dbrow.Content,
Vote = dbrow.Vote,
Date = dbrow.Date
};
if (dbrow.Attitude.Equals("Negative", StringComparison.InvariantCultureIgnoreCase))
{
item.NegativeComments.Add(sentiments);
}
else if (dbrow.Attitude.Equals("Positive", StringComparison.InvariantCultureIgnoreCase))
{
item.PositiveComments.Add(sentiments);
}
result.Add(item);
}
return(result);
}
}
/// <summary>
/// Builds the hash table.
/// </summary>
/// <param name="userid">The userid.</param>
/// <param name="userFilter">The user filter.</param>
public void BuildHashTable(string userid, CustomerFilters userFilter)
{
//this.ReadStopWords(@"..\..\DataFiles\english.stop");
long docID = 0;
// var stemer = new EnglishStemmer();
// SentimentManager sentiManager = new SentimentManager();
var filter = this.indexHelper.BuildPatternFromFilter(userFilter);
var newslines = this.repository.GetKeyWords(userid, filter);
foreach (var newsline in newslines)
{
docID = newsline.Id;
DocScore.Add(docID, (double)newsline.Score);
var subStrings = Regex.Split(newsline.KeyWords.Trim(), " ");
foreach (var subString in subStrings)
{
var word = subString.ToLower();
if (this.WordDoc.ContainsKey(word))
{
var value = this.WordDoc[word];
if (value.ContainsKey(docID))
{
value[docID]++;
}
else
{
value.Add(docID, 1);
}
}
else
{
var value = new Dictionary <long, int> {
{ docID, 1 }
};
this.WordDoc.Add(word, value);
}
// DocWord
if (this.DocWord.ContainsKey(docID))
{
var value = this.DocWord[docID];
if (value.ContainsKey(word))
{
value[word]++;
}
else
{
value.Add(word, 1);
}
}
else
{
var value = new Dictionary <string, int> {
{ word, 1 }
};
this.DocWord.Add(docID, value);
}
// }
}
}
}
public static IQueryable <OrganizationCustomer> ApplyWhereFilters(this IQueryable <OrganizationCustomer> entities,
CustomerFilters filters)
{
return(entities.Where(WhereFilter(filters)));
}