public async Task <IEnumerable <Contact> > GetContactsAsync(string userId)
{
var sql = "SELECT \"Id\", \"Online\", \"Other\" AS \"Uid\", \"Title\" as \"Name\", COALESCE((SELECT \"Time\" FROM \"ChatRecords\" WHERE \"ChatId\" = a.\"Id\" ORDER BY \"Time\" DESC LIMIT 1),\"UpdateTime\") AS \"Time\",\"GroupName\", COALESCE((SELECT \"Unread\" FROM \"ChatRecords\" WHERE \"ChatId\" = a.\"Id\" ORDER BY \"Unread\" DESC LIMIT 1), FALSE) AS \"Unread\", COALESCE((SELECT \"Content\" FROM \"ChatRecords\" WHERE \"ChatId\" = a.\"Id\" ORDER BY \"Time\" DESC LIMIT 1),'') AS \"Message\" FROM \"Chats\" AS a WHERE \"Owner\" = @userId AND \"DisplayInList\" AND \"ServiceRecordId\" IS NUll ORDER BY \"Online\" DESC, \"Time\" DESC";
var list = await _dbContext.SqlQueryAsync <Contact>(sql, new { userId });
return(list.Where(item => item.Time.HasValue));
}
private async Task <List <QandA> > GetAnswers(string intent, List <long> departIds)
{
var sql = new StringBuilder();
sql.Append("SELECT a.* FROM \"Answers\" AS a WHERE a.\"Intent\"=@intent");
if (departIds?.Count > 0)
{
var ids = string.Join(",", departIds);
sql.Append($" AND(a.\"Id\" IN(SELECT DISTINCT b.\"QAId\" FROM \"QAPermissions\" AS b WHERE b.\"DepartmentId\" IN({ids})) OR a.\"IsPublic\" = TRUE )");
}
else
{
sql.Append(" AND a.\"IsPublic\" = TRUE");
}
// SELECT a.* FROM "Answers" AS a WHERE a."Intent"='问候' AND(a."Id" IN(SELECT DISTINCT b."QAId" FROM "QAPermissions" AS b WHERE b."DepartmentId" IN(1, 2, 3)) OR a."IsPublic" = TRUE )
var answers = await _context.SqlQueryAsync <QandA>(sql.ToString(), new { intent }); //await _cache.StringGetAsync<List<QandA>>(intent);
//if (answers == null)
//{
// answers = await _context.Answers
// .Where(a => a.Intent.Equals(intent, StringComparison.CurrentCultureIgnoreCase))?
// .ToListAsync();
// if (answers != null)
// {
// await _cache.StringSetAsync(intent, answers, TimeSpan.FromMinutes(20));
// }
//}
return(answers?.ToList());
}
// TODO : 反馈统计
// select "Intent","FeedbackResults",COUNT(*) as "Count" from "FeedbackInfos" GROUP BY "Intent","FeedbackResults" HAVING "FeedbackResults"=1 ORDER BY "Count" DESC LIMIT 10
public async Task <IList <FeedbackStatisticsResult> > FeedbackStatistics(int result, int limit = 10)
{
var sql = $"select \"Intent\",COUNT(*) as \"Count\" from \"FeedbackInfos\" GROUP BY \"Intent\",\"FeedbackResults\" HAVING \"FeedbackResults\"={result} ORDER BY \"Count\" DESC LIMIT {limit}";
var data = await _context.SqlQueryAsync <FeedbackStatisticsResult>(sql);
return(data?.ToList());
}
public async Task <IList <ModularResponseResult> > FilterModulePermissionAsync(List <long> departIds, List <ModularResponseResult> source)
{
if (source?.Count > 0)
{
var sql = string.Empty;
if (departIds?.Count > 0)
{
sql = $"SELECT a.* FROM \"Modulars\" a LEFT JOIN \"ModularPermissions\" b ON a.\"Id\" = b.\"ModularId\" WHERE b.\"DepartmentId\" IN({string.Join(",", departIds)}) OR a.\"IsPublic\" = TRUE";
}
else
{
sql = "SELECT a.* FROM \"Modulars\" a LEFT JOIN \"ModularPermissions\" b ON a.\"Id\" = b.\"ModularId\" WHERE a.\"IsPublic\" = TRUE";
}
var queryResult = await _dbContext.SqlQueryAsync <Modular>(sql);
var modules = queryResult?.ToList();
if (modules?.Count > 0)
{
return(source.Where(s => modules.Any(m => m.ModularId == s.ListName)).ToList());
}
}
return(new List <ModularResponseResult>());
}
public async Task <IEnumerable <HotIntentBarViewModel> > GetHotIntentBarTopTen()
{
var sql = "SELECT * FROM \"HotIntents\" ORDER BY \"Count\" DESC LIMIT 10";
return(await _dbContext.SqlQueryAsync <HotIntentBarViewModel>(sql));
}
public async Task <IList <EdgeDto> > GetMapEdges(long mapId)
{
var sql = "SELECT DISTINCT \"Id\" AS \"source\",\"NextStep\" AS \"target\", NULL AS \"Label\" FROM \"Steps\" AS a WHERE \"MapId\"=@mapId AND a.\"NextStep\" <> 0 AND a.\"Id\" <> 0 UNION SELECT \"PrevStep\" AS \"source\", \"Id\" AS \"target\", \"TriggeredResult\" AS \"Label\" FROM \"Steps\" AS b WHERE \"MapId\" = @mapId AND b.\"PrevStep\" <> 0 and b.\"Id\" <> 0";
return((await _dbContext.SqlQueryAsync <EdgeDto>(sql, new { mapId }))?.ToList());
}